﻿/*
 * wqh_2013-991-22
 * 执行sql语句操作MSSQL数据库
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace Help
{

    /**************************************************************************************************************
    *	作者：XJB
    *	创建日期：2014/4/9 14:56:24
    *	版本号：1.0.0
    *	描述：将常见数据库操作进行封装
    *==================================================================
    *	修改标记：1
    *	修改时间：2014/4/9 14:56:24
    *	修改人：XJB
    *	描述：新建；
    *	版本号：1.0.0
    ***************************************************************************************************************/

    public class ComdSqlFactory
    {
        #region 定义变量
        private static string conString = ALL_DataV.Constring;
        // private static SqlConnection con = null;
        // string ad = "";
        #endregion

        #region 构造函数
        ///// <summary>
        ///// 构造函数
        ///// </summary>
        //public ComdSqlFactory()
        //{
        //    try
        //    {
        //        con = new SqlConnection(conString);
        //    }
        //    catch (Exception ex)
        //    {
        //        WriteLog.WriteW("数据库连接异常：" + ex.Message);
        //    }
        //}

        ///// <summary>
        ///// 构造函数
        ///// </summary>
        ///// <param name="cString">数据库链接字符串</param>
        //public ComdSqlFactory(string cString)
        //{
        //    try
        //    {
        //        conString = cString;
        //        con = new SqlConnection(conString);
        //    }
        //    catch (Exception ex)
        //    {
        //        WriteLog.WriteW("数据库连接异常：" + ex.Message);
        //    }
        //}
        #endregion

        #region 执行sql更新删除插入数据库操作,成功则返回true
        /// <summary>   
        /// 功能：执行更新删除插入数据库操作,成功则返回true   
        /// </summary>   
        /// <param name="strSql">sql操作语句</param>   
        /// <returns>true/false</returns>   
        public bool ExecuteScalar(string strSql)
        {
            bool flag = false;

            //操作   
            SqlConnection con = new SqlConnection(conString);
            SqlCommand cm = new SqlCommand();
            cm.Connection = con;
            cm.CommandText = strSql;
            try
            {

                con.Open();
                if (cm.ExecuteNonQuery() > 0)
                    flag = true;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("执行sql更新删除插入数据库操作：" + ex.Message+" SQL:"+strSql);
            }
            finally
            {
                con.Close();
            }
            return flag;
        }

        
        /// <summary>
        /// 功能：将二进制数组更新到数据库操作,成功则返回true  
        /// </summary>
        /// <param name="strSql">SQL操作语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="by">二进制数据</param>
        /// <returns></returns>
        public bool ExecuteScalar(string strSql, SqlParameter[] parameters, Byte[] by)
        {
            bool flag = false;
            SqlConnection con = new SqlConnection(conString);
            try
            {
                SqlCommand comm = con.CreateCommand();
                comm.CommandText = strSql;
                for (int i = 0; i < parameters.Length; i++)
                {
                    comm.Parameters.Add(parameters[i]);
                }

                con.Open();
                if (comm.ExecuteNonQuery() > 0)
                    flag = true;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("将二进制数组更新到数据库操作出现异常：" + ex.Message);
            }
            finally
            {
                con.Close();
            }
            return flag;
        }
        #endregion

        #region 执行查询数据库操作是否有记录
        /// <summary>   
        /// 功能：执行查询数据库操作是否有记录　   
        /// </summary>   
        /// <param name="strSql">sql查询语句</param>   
        /// <returns>true/false</returns>   
        public bool ExecuteScalarRead(string strSql)
        {
            SqlConnection con = new SqlConnection(conString);
            con.Open();
            //WriteLog.WriteW("数据库查询02");
            SqlCommand cm = new SqlCommand();
            //WriteLog.WriteW("数据库查询03");
            cm.CommandText = strSql;
            //WriteLog.WriteW("数据库查询04");
            cm.Connection = con;
            try
            {
                //WriteLog.WriteW("数据库查询05");
                SqlDataReader dr = cm.ExecuteReader();
                //WriteLog.WriteW("数据库查询06");
                if (dr.Read())
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("执行查询数据库操作是否有记录：" + ex.Message);
                return false;
            }
            finally
            {
                cm.Dispose();
                con.Close();
            }
        }
        #endregion

        #region 执行查询数据库操作

        /// <summary>   
        /// 功能：执行查询操作   
        /// </summary>   
        /// <param name="strSql"></param>   
        /// <param name="ds">返回数据集</param>   
        /// <returns></returns>   
        public bool ExecuteSelectCmmond(string strSQL, DataSet ds)
        {
            bool res = false;
            SqlConnection con = new SqlConnection(conString);
            SqlCommand comd = new SqlCommand();
           // WriteLog.WriteW("执行数据库查询操作:" + strSQL );
            try
            {
                con.Open();
                comd.CommandText = strSQL;
                comd.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(comd);
                da.Fill(ds);
                res = true;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("执行数据库查询操作"+strSQL+"异常：" + ex.Message);
            }
            finally
            {
                comd.Dispose();
                con.Close();
            }
            return res;
        }
        #endregion

        #region 执行存储过程
        public bool ExecuteProcCmmond(string proc, SqlParameter[] parameters, DataSet ds)
        {
            bool res = false;
            SqlConnection con = new SqlConnection(conString);
            SqlCommand comd = new SqlCommand();
            try
            {
                con.Open();
                comd.CommandType = CommandType.StoredProcedure;//设置cmd的类型为存储过程
                comd.CommandText = proc;
                comd.Connection = con;
                for (int i = 0; i < parameters.Length; i++)
                {
                    comd.Parameters.Add(parameters[i]);
                }
                SqlDataAdapter da = new SqlDataAdapter(comd);
                da.Fill(ds);
                res = true;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("执行存储过程异常：" + ex.Message);
            }
            finally
            {
                comd.Parameters.Clear();
                comd.Dispose();
                con.Close();
            }
            return res;
        }
        #endregion

        #region 读取特定行
        public string ReaderGetRow(string sql)
        {
            SqlCommand comd = new SqlCommand(); //创建命令对象
            SqlConnection con = new SqlConnection(conString);
            try
            {
                con.Open();
                comd.Connection = con;//设置连接
                comd.CommandText = sql;          //提供SQL命令
                SqlDataReader myReader = comd.ExecuteReader();  //执行命令返回结果指派给DataReader对象
                string res = "-99";
                while (myReader.Read()) //从读取器中获取结果
                {
                    if (myReader["id"].ToString() == "1")
                    {
                        res = myReader["ReadLog"].ToString();
                        break;
                    }
                }
                return res;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("读取特定行异常：" + ex.Message);
                return "-99";
            }
            finally
            {
                con.Dispose();
            }
        }
        #endregion

        #region 返回头行头列
        /// <summary>
        /// 返回头行头列
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回""无数据</returns>
        public string firstrow(string sql)
        {
            SqlConnection con = new SqlConnection(conString);
            SqlCommand comd = new SqlCommand(); //创建命令对象
            try
            {
                string s = "";
                con.Open();
                comd.Connection = con;//设置连接
                comd.CommandText = sql;          //提供SQL命令
                object ret = comd.ExecuteScalar();
                if (ret == null)
                {
                    s = "";
                }
                else
                {
                    s = ret.ToString(); 
                }
                return s;
            }
            catch (Exception ex)
            {
                WriteLog.WriteW("返回第一行第一列异常：" + ex.Message);
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }
        #endregion

        #region 事务处理类
        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="constring"></param>
        public bool UpdateTransaction(string[] sql)
        {
            bool isSuccess = false;
            int row = -1;// 表示受影响的行数
            SqlConnection con = new SqlConnection(conString);
            con.Open();
            SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sql.Length; i++)
                {
                    if (!sql[i].Equals(""))
                    {
                        SqlCommand cmd = new SqlCommand(sql[i], con, trans);
                        row = cmd.ExecuteNonQuery();
                        if (row < 0)// 受影响的行数<0表示SQL为执行成功
                        {
                            return false;
                        }
                        else
                        {
                            isSuccess = true;
                        }
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                WriteLog.WriteW("执行事务处理出现异常：" + ex.Message);
                isSuccess = false;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return isSuccess;
        }

        /// <summary>
        /// 事务处理
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="constring"></param>
        public bool UpdateTransaction(StringBuilder[] sql)
        {
            bool isSuccess = false;
            int row = -1;// 表示受影响的行数
            SqlConnection con = new SqlConnection(conString);
            con.Open();
            SqlTransaction trans = con.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                for (int i = 0; i < sql.Length; i++)
                {
                    SqlCommand cmd = new SqlCommand(sql[i].ToString(), con, trans);
                    row = cmd.ExecuteNonQuery();
                    if (row < 0)// 受影响的行数<0表示SQL为执行成功
                    {
                        return false;
                    }
                    else
                    {
                        isSuccess = true;
                    }
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                WriteLog.WriteW("执行事务处理出现异常：" + ex.Message);
                isSuccess = false;
            }
            finally
            {
                con.Close();
                con.Dispose();
            }
            return isSuccess;
        }
        #endregion

    }
}
